package com.summit.exam.dbdao;

import com.summit.exam.MysqlConnection;
import com.summit.exam.entity.UserInfoBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserInfoDao {

    /**
     * fun: 插入一个学生
     * @param stuId
     * @param stuPassword
     * @param stuName
     * @param stuSex
     * @return void
     */
    public boolean insertStudent(String stuId, String stuPassword, String stuName, String stuSex) {
        // 取得数据库连接
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        String sql = "insert into student (stu_id, stu_password, stu_name, stu_sex) values(?, ?, ?, ?)";
        int rs = 0;

        PreparedStatement stm = null;
        try {

            stm = conn.prepareStatement(sql);
            stm.setString(1, stuId);
            stm.setString(2, stuPassword);
            stm.setString(3, stuName);
            stm.setString(4, stuSex);
            rs = stm.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return (rs != 0);
    }

    /**
     * fun: 删除一个学生
     * @param stuId
     * @return void
     */
    public boolean deleteStudent(String stuId) {
        // TODO Auto-generated method stub
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        String sql = "delete from user_info where id = ?";
        PreparedStatement stm = null;
        int rs = 0;
        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1,stuId);
            rs = stm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return rs != 0;
    }

    /**
     * fun: 修改一个学生的信息
     * @param stuId
     * @param stuPassword
     * @param stuName
     * @param stuSex
     * @return void
     */
    public boolean updateStudent(String stuId, String stuPassword, String stuName, String stuSex){
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        String sql = "update student set stu_password = ?, stu_name = ?, stu_sex = ? where stu_id = ?";
        PreparedStatement stm = null;
        int rs = 0;

        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1,stuPassword);
            stm.setString(2,stuName);
            stm.setString(3,stuSex);
            stm.setString(4,stuId);
            rs = stm.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return rs != 0;
    }

    /**
     *修改密码
     * @param stuId
     * @param newPsw
     * @return
     */
    public Boolean updatePassword(String stuId, String newPsw){
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        String sql = "update user_info set user_login_pwd = ? where user_login_name = ?;";
        PreparedStatement stm = null;

        int ok = 0;
        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1,newPsw);
            stm.setString(2,stuId);
            ok = stm.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return (ok != 0);
    }

    /**
     * 更新名字和性别
     * @param stuId
     * @param stuName
     * @param stuSex
     * @return
     */
    public Boolean  updateNameAndSex(String stuId,String stuName,String stuSex){
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();

        String sql = "update user_info set user_login_name = ?,user_sex = ? where user_login_name = ?;";
        PreparedStatement stm = null;

        int ok = 0;
        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1,stuName);
            stm.setString(2,stuSex);
            stm.setString(3,stuId);
            ok = stm.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return (ok != 0);
    }
    /**
     * fun: 通过names查询一个学生的所有信息
     * @param names
     * @return Student
     */
    public ArrayList<UserInfoBean> queryStudent(String names){
        ArrayList<UserInfoBean> arrayList = new ArrayList<UserInfoBean>();
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        String sql = "select * from user_info where user_login_name LIKE '%" + names + "%' ";
        System.out.println(sql);
        PreparedStatement stm = null;
        ResultSet rs = null;

        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            if(rs.next()){
                UserInfoBean student = new UserInfoBean();
                student.setUserId(rs.getString("id"));
                student.setUserName(rs.getString("user_name"));
                student.setUserSex(rs.getString("user_sex"));
                student.setUserLoginName(rs.getString("user_login_name"));
                student.setUserLoginPwd(rs.getString("user_login_pwd"));
                student.setPlaintPwd(rs.getString("plaint_pwd"));
                student.setUserHeadUrl(rs.getString("user_head_url"));
                student.setUserTel(rs.getString("user_tel"));
                student.setUserLevel(rs.getString("user_level"));
                student.setCreateTime(rs.getString("create_time"));
                student.setCreateUserId(rs.getString("create_user_id"));
                student.setCreateUserName(rs.getString("create_user_name"));
                arrayList.add(student);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                MysqlConnection.CloseConn(rs, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return arrayList;
    }

    /**
     * fun: 查询所有读者的所有信息
     * @return ArrayList<Student>
     */
    public ArrayList<UserInfoBean> queryAllStudent(){
        ArrayList<UserInfoBean> students_Array = new ArrayList<UserInfoBean>();
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        String sql = "select * from student";

        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while(rs.next()){
                UserInfoBean student = new UserInfoBean();
                student.setUserId(rs.getString("id"));
                student.setUserName(rs.getString("user_name"));
                student.setUserSex(rs.getString("user_sex"));
                students_Array.add(student);
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                MysqlConnection.CloseConn(rs, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return students_Array;
    }

    public UserInfoBean loginSystem(String username, String password) {
        UserInfoBean userInfoBean = new UserInfoBean();
        Connection conn = MysqlConnection.getMysqlConnection().getCon();
        PreparedStatement pst = null;
        ResultSet rs = null;

        System.out.println(username);
        try {
            pst = conn.prepareStatement("SELECT * FROM user_info WHERE user_login_name = ? AND user_login_pwd = ?");
            pst.setString(1, username);
            pst.setString(2, password);
            rs = pst.executeQuery();

            while (rs.next()) {
                userInfoBean.setUserId(rs.getString(1));
                userInfoBean.setUserLoginName(rs.getString(2));
                userInfoBean.setUserLoginPwd(rs.getString(3));
                userInfoBean.setPlaintPwd(rs.getString(4));
                userInfoBean.setUserName(rs.getString(5));
                userInfoBean.setUserHeadUrl(rs.getString(6));
                userInfoBean.setUserSex(rs.getString(7));
                userInfoBean.setUserTel(rs.getString(8));
                userInfoBean.setUserLevel(rs.getString(9));
                userInfoBean.setCreateTime(rs.getString(10));
                userInfoBean.setCreateUserId(rs.getString(11));
                userInfoBean.setCreateUserName(rs.getString(12));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(rs, pst, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return userInfoBean;
    }

    public boolean insertUser(String userId, String userLogName, String userLogPassword,
             String userName, String userHeadUrl, String userSex, String userTel, String userLevel
            , String createTime, String createNd, String createName) {
        // 取得数据库连接
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        String sql = "insert into user_info (id, user_login_name, user_login_pwd, " +
                "plaint_pwd, user_name, user_head_url, user_sex, user_tel, user_level, " +
                "create_time, create_user_id, create_user_name) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        int rs = 0;

        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1, userId);
            stm.setString(2, userLogName);
            stm.setString(3, userLogPassword);
            stm.setString(4, userLogPassword);
            stm.setString(5, userName);
            stm.setString(6, userHeadUrl);
            stm.setString(7, userSex);
            stm.setString(8, userTel);
            stm.setString(9, userLevel);
            stm.setString(10, createTime);
            stm.setString(11, createNd);
            stm.setString(12, createName);
            rs = stm.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return (rs != 0);
    }
}
